package com.tjtt.tdm.system.dao.impl;
import java.util.List;
import java.util.Map;

import org.hibernate.Query;
import org.hibernate.SQLQuery;

import com.tjtt.common.utils.StringUtil;
import com.tjtt.tdm.base.HibernateEntityDAO;
import com.tjtt.tdm.base.PageResult;
import com.tjtt.tdm.system.bean.Organization;
import com.tjtt.tdm.system.bean.Users;
import com.tjtt.tdm.system.dao.UserDao;

public class UserDaoImpl extends HibernateEntityDAO<Users> implements UserDao {
    //通过主键id查询用户信息
	public Users getUsersById(long userid) {
		Users users=(Users) super.getHibernateTemplate().get(Users.class ,userid);
		 return users;
	}
//	/**
//	 * 根据父节点ID查询Users表的所有子节点
//	 * 
//	 * @param userId
//	 * @return
//	 * @throws DaoException
//	 */ 
//	
//	public List<Users> findUsers() throws DaoException {
//		String hql = "from Users as users where 1=1";
//		return this.findByHql(hql);
//	}

	//根据登录名查询用户
	public Users findUsersByName(String loginname) {
		String sql = "from Users u where u.loginname = ? and u.delFlag = ?";
		Query sqlQuery = this.getSession().createQuery(sql);
		sqlQuery.setParameter(0, loginname);
		sqlQuery.setParameter(1, "1");
		List list = sqlQuery.list();
		if(list!=null&&list.size()>0){
			Users users = (Users) list.get(0);
			return users;
		}else{
			return null;
		}
	}

	public String findUserDelflagByOrgId(String ids) {
		String sql = " select t.del_flag from sys_users t left join sys_organization o on t.org_id=o.org_id where o.org_id= '"+ids+"' ";
		Query sqlQuery = this.getSession().createSQLQuery(sql);
		//sqlQuery.setParameter(0, orgId);
		List list = sqlQuery.list();
		if(list!=null && list.size()>0){
			String delFlag = list.toString();
			return delFlag;
		}else{
			return "";
		}
	}

	//根据组织ID查询用户
	public List<Users> findUserByOrgId(int start,int limit,Long orgId) {
		String sql = "select * from (select * from sys_users u left join sys_organization o " +
						"on u.org_id=o.org_id start with o.org_id= '"+orgId+"'" +
						" connect by prior o.org_id = o.org_parent_id " +
						"and u.del_flag = '1' ) where rownum >= 0 and rownum<= 15 ";
		SQLQuery sqlQuery = this.getSession().createSQLQuery(sql);
	/*	sqlQuery.setParameter(0, start);
		sqlQuery.setParameter(1, limit);*/
		sqlQuery.addEntity(Users.class);
		List<Users> list = sqlQuery.list();
		return list;
	}
	
	public PageResult queryUserList(Map filterMap){
		StringBuffer queryString = new StringBuffer();
//		queryString.append("select distinct u.userid,u.loginname,u.username,u.sex as sexLabel,u.job_name,u.org_id,u.active,u.lock_flag,o.org_name from sys_organization o left join sys_users u  " +
//		"on u.org_id=o.org_id where u.del_flag = '1' and u.userid<>4 ");
//		queryString.append("select distinct u.userid,u.loginname,u.username,u.sex as sexLabel,u.job_name,u.org_id,u.active,u.lock_flag,o.org_name from sys_organization o left join sys_users u  " +
//						"on u.org_id=o.org_id where 1=1 ");
		
		queryString.append("select distinct u.userid,u.loginname,u.username,u.sex as sexLabel,u.job_name,u.org_id,u.active,u.lock_flag,o.org_name from sys_users u  left join sys_organization o " +
		"on u.org_id=o.org_id where 1=1 ");
		
		StringBuffer countString = new StringBuffer();
//		countString.append("select count(*) from (select distinct userid from sys_organization o left join sys_users u " +
//						"on u.org_id=o.org_id where u.del_flag = '1' and u.userid<>4  ");
//		countString.append("select count(*) from (select distinct userid from sys_organization o left join sys_users u " +
//		"on u.org_id=o.org_id where 1=1 ");
		
		countString.append("select count(*) from (select distinct u.userid,u.loginname,u.username,u.sex as sexLabel,u.job_name,u.org_id,u.active,u.lock_flag,o.org_name from sys_users u  left join sys_organization o " +
		"on u.org_id=o.org_id where 1=1");
		if (filterMap != null && !filterMap.isEmpty()) {
			String filterProperty;
			String filterValue;			 
			filterProperty = "orgId";
			filterValue = (String) filterMap.get(filterProperty);
			if (!StringUtil.isMissing(filterValue)) {
				//queryString.append(" start with o.org_id = '").append(filterValue).append("' connect by prior o.org_id = o.org_parent_id");
				//countString.append(" start with o.org_id = '").append(filterValue).append("' connect by prior o.org_id = o.org_parent_id )");
				//queryString.append(" where o.org_id = '").append(filterValue).append("' connect by prior o.org_id = o.org_parent_id");
				//countString.append(" start with o.org_id = '").append(filterValue).append("' connect by prior o.org_id = o.org_parent_id )");
				queryString.append(" and o.org_id = '").append(filterValue).append("'");
				countString.append(" and o.org_id = '").append(filterValue).append("'");
			} 
		}
		countString.append(") ");
		String sidx = (String)filterMap.get("sidx");
		String sord = (String)filterMap.get("sord");
		queryString.append(" order by u." + sidx + " " + sord );
		
		return searchBySQL(countString.toString(),queryString.toString(),filterMap);	
	}

	@Override//查询所有用户
	public PageResult queryUser(Map filterMap) {
		StringBuffer queryString = new StringBuffer();
		queryString.append("select distinct u.userid,u.username,u.org_id,o.org_name from sys_users u left join sys_organization o on u.org_id = o.org_id where u.del_flag = '1' ");
		StringBuffer queryCountString = new StringBuffer();
		queryCountString.append(" select count(*) from (select distinct u.userid,u.username,u.org_id,o.org_name from sys_users u left join sys_organization o on u.org_id = o.org_id where u.del_flag = '1' )");
		if (filterMap != null && !filterMap.isEmpty()) {
			if (null != filterMap.get("username")&& !"".equals(filterMap.get("username"))) {
				queryString.append("and u.USERNAME like '%"+ filterMap.get("username") + "%'  ");
				queryCountString.append("and u.USERNAME like '%"+ filterMap.get("username") + "%'  ");
			}
			if (null != filterMap.get("org_name")&& !"".equals(filterMap.get("org_name"))) {
				queryString.append("and o.org_name like '%"+ filterMap.get("org_name") + "%'  ");
				queryCountString.append("and o.org_name like '%"+ filterMap.get("org_name") + "%'  ");
			}
			String sidx = (String)filterMap.get("sidx");
			String sord = (String)filterMap.get("sord");
			queryString.append(" order by u." + sidx + " " + sord );
		}
		return searchBySQL(queryCountString.toString(),queryString.toString(),filterMap);
	}

	@Override
	public Users findUsersByloginName(String loginname) {
		List userList = this.getHibernateTemplate().find("from Users o where o.loginname='" + StringUtil.escapeString(loginname) + "' and o.delFlag=1 ");
		if (userList != null && userList.size() > 0) {
			return (Users) userList.get(0);
		}
		return null;
	}

	@Override
	public Organization getOrganationByorgId(Long orgId) {
		Organization organization=(Organization) super.getHibernateTemplate().get(Organization.class ,orgId);
		 return organization;
	}

	@Override
	public PageResult queryUserList2(Map<String, String> filterMap) {
		StringBuffer queryString = new StringBuffer();
		queryString.append("select distinct u.userid,u.loginname,u.username,u.sex as sexLabel,u.job_name,u.org_id,u.active,u.lock_flag,o.org_name from sys_users u left join sys_organization o " +
						"on u.org_id=o.org_id where u.del_flag = '1' ");
		StringBuffer countString = new StringBuffer();
		countString.append("select count(*) from (select distinct userid from sys_users u left join sys_organization o " +
						"on u.org_id=o.org_id where u.del_flag = '1'  ");
		if (filterMap != null && !filterMap.isEmpty()) {
			String filterProperty;
			String filterValue;
			
			filterProperty = "orgId";
			filterValue = (String) filterMap.get(filterProperty);
			if (!StringUtil.isMissing(filterValue)) {
				queryString.append(" start with o.org_id = '").append(filterValue).append("' connect by prior o.org_id = o.org_parent_id");
				countString.append(" start with o.org_id = '").append(filterValue).append("' connect by prior o.org_id = o.org_parent_id )");
			} 
			if(null != filterMap.get("loginname") && !"".equals(filterMap.get("loginname"))){
				queryString.append("and u.LOGINNAME like '%" +  StringUtil.escapeString(String.valueOf(filterMap.get("loginname"))) + "%' ");
				countString.append("and u.LOGINNAME like '%" +  StringUtil.escapeString(String.valueOf(filterMap.get("loginname"))) + "%' )");
			}
			if(null != filterMap.get("username") && !"".equals(filterMap.get("username"))){
				queryString.append("and u.USERNAME like '%" + filterMap.get("username") + "%'  ");
				countString.append("and u.USERNAME like '%" + filterMap.get("username") + "%'  )");
			}
			if(null != filterMap.get("activeLabel") && !"".equals(filterMap.get("activeLabel"))){
				queryString.append("and u.ACTIVE like '%" + filterMap.get("activeLabel") + "%' ");
				countString.append("and u.ACTIVE like '%" + filterMap.get("activeLabel") + "%' )");
			}
			if(null != filterMap.get("lockFlagLabel") && !"".equals(filterMap.get("lockFlagLabel"))){
				queryString.append("and  u.LOCK_FLAG like '%" + filterMap.get("lockFlagLabel") + "%' ");
				countString.append("and  u.LOCK_FLAG like '%" + filterMap.get("lockFlagLabel") + "%' )");
			}
			if(null != filterMap.get("orgId") && !"".equals(filterMap.get("orgId"))){
				queryString.append("and o.org_id like '%" + filterMap.get("orgId") + "%' ");
				countString.append("and o.org_id like '%" + filterMap.get("orgId") + "%' )");
			}
			String sidx = (String)filterMap.get("sidx");
			String sord = (String)filterMap.get("sord");
			queryString.append(" order by u." + sidx + " " + sord );
		}
		
		return searchBySQL(countString.toString(),queryString.toString(),filterMap);	
	}
	
	public Users findUsersByUserName(String username) {
		List userList = this.getHibernateTemplate().find("from Users o where o.username='" + username + "' and o.delFlag=1 ");
		if (userList != null && userList.size() > 0) {
			return (Users) userList.get(0);
		}
		return null;
	}
	public PageResult queryChooseData(Map<String, String> filterMap) {
		StringBuffer queryString = new StringBuffer();
		StringBuffer queryCountString = new StringBuffer();
		queryCountString.append(" select count(*) from "+filterMap.get("tableName")+" t  where 1=1  " );
		queryString.append("select "+filterMap.get("columnName")+" from "+filterMap.get("tableName")+" t where 1=1 " );
//		if (filterMap != null && !filterMap.isEmpty()) {
//			if (filterMap.get("userIds") != null&&!"null".equals(filterMap.get("userIds"))) {
//				queryCountString.append(" and t.userid in ("+filterMap.get("userIds")+") " );
//				queryString.append(" and t.userid in ("+filterMap.get("userIds")+") " );
//			}
//		}
		String sidx = (String) filterMap.get("sidx");
		String sord = (String) filterMap.get("sord");
		queryString.append(" order by " + sidx + " " + sord + " ");
		return searchBySQL(queryCountString.toString(), queryString.toString(),
				filterMap);
	}
	
	public List getDatabySql(String sql) {
		Query query = getSession().createSQLQuery(sql);
		List l =query.list(); 
		return l;
	}

}
